﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using FlotasDesktop;
using System.Data.SqlClient;

namespace GestorDeFlotasDesktop.AbmChoferAuto
{
    public partial class AbmChoferAutoIntegrado : Form
    {
        String conexion;
        public AbmChoferAutoIntegrado()
        {
            InitializeComponent();
            Utils utils = new Utils();
            conexion = utils.cargarProperties();
            this.initializeChofer();
            this.initializeAuto();
            this.initializeTurno();
            this.cargarRelacionChoferAuto();
        }

        private void cargarRelacionChoferAuto()
        {
            using (SqlConnection connection = new SqlConnection(conexion))
            {
                try
                {
                    string sentSql = @"SELECT T.descripcion Turno, (CH.apellido + ', ' + CH.nombre) Chofer, ch.Dni, CH.habilitado  Chofer_Habilitado, A.PATENTE, A.MODELO, MA.DESCRIPCION Marca, A.HABILITADO Automovil_Habilitado
                    FROM TEQUILASO.CHOFER CH, TEQUILASO.AUTOMOVIL A, TEQUILASO.CHOFER_AUTOMOVIL CH_A, TEQUILASO.MARCA_AUTOMOVIL MA, TEQUILASO.TURNO T
                    WHERE CH_A.id_chofer = CH.id
                    AND CH_A.id_automovil = A.id
                    AND CH_A.id_turno = T.id
                    AND A.id_marca = MA.ID";
                    SqlConnection sqlConn = new SqlConnection(conexion);
                    sqlConn.Open();
                    DataTable objDataTable = new DataTable();
                    SqlDataAdapter dataAdap = new SqlDataAdapter(sentSql, conexion);
                    dataAdap.Fill(objDataTable);
                    dataGridChoferAuto.DataSource = (objDataTable);
                    connection.Close();

                }
                catch (Exception ex)
                {
                    MessageBox.Show("Error en la tabla Funcionalidad");
                }
            }
        }

        private void initializeTurno()
        {
            using (SqlConnection connection = new SqlConnection(conexion))
            {
                try
                {
                    using (SqlCommand command = new SqlCommand())
                    {
                        command.Connection = connection;
                        command.CommandType = System.Data.CommandType.StoredProcedure;
                        command.CommandText = "TEQUILASO.sp_turnoHabilitado";
                        using (SqlDataAdapter adapter = new SqlDataAdapter(command))
                        {
                            DataTable dt = new DataTable();
                            adapter.Fill(dt);
                            turnoComboBox.DataSource = dt;
                            turnoComboBox.DisplayMember = "descripcion";

                        }

                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
            }

        }
        private void initializeAuto()
        {
            using (SqlConnection connection = new SqlConnection(conexion))
            {
                try
                {
                    using (SqlCommand command = new SqlCommand())
                    {

                        connection.Open();
                        command.Connection = connection;
                        command.CommandType = System.Data.CommandType.StoredProcedure;
                        command.CommandText = "TEQUILASO.sp_autoHabilitado";

                        using (SqlDataAdapter adapter = new SqlDataAdapter(command))
                        {
                            DataSet ds = new DataSet();
                            adapter.Fill(ds);
                            dataGridView2.DataSource = ds.Tables[0];

                        }


                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
            }
 
        }

       private void initializeChofer()
        {
           using (SqlConnection connection = new SqlConnection(conexion))
            {
            try
                {
                    using (SqlCommand command = new SqlCommand())
                    {

                        connection.Open();
                        command.Connection = connection;
                        command.CommandType = System.Data.CommandType.StoredProcedure;
                        command.CommandText = "TEQUILASO.sp_choferHabilitado";
                        using (SqlDataAdapter adapter = new SqlDataAdapter(command))
                        {
                            DataSet ds = new DataSet();
                            adapter.Fill(ds);
                            dataGridView1.DataSource = ds.Tables[0];

                        }

                        
                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
            }
        }
        private void button1_Click(object sender, EventArgs e)
        {
            using (SqlConnection connection = new SqlConnection(conexion))
            {
                try
                {
                    using (SqlCommand command = new SqlCommand())
                    {
                        connection.Open();
                        command.Connection = connection;
                        command.CommandType = System.Data.CommandType.StoredProcedure;
                        command.CommandText = "TEQUILASO.sp_relacionChoferAuto";
                        command.Parameters.AddWithValue("@IdAuto", Convert.ToInt32(dataGridView2.CurrentRow.Cells[0].Value.ToString()));
                        command.Parameters.AddWithValue("@IdChofer", Convert.ToInt32(dataGridView1.CurrentRow.Cells[0].Value.ToString()));
                        command.Parameters.AddWithValue("@Fecha", desdePicker.Value);
                        command.Parameters.AddWithValue("@DescripcionTurno", turnoComboBox.Text);
                        command.ExecuteReader();
                        connection.Close();

                        MessageBox.Show("Relacion realizada con exito");

                        cargarRelacionChoferAuto();
                        dataGridChoferAuto.Refresh();


                    }
                }
                catch (SqlException ex)
                {
                    MessageBox.Show(ex.Message, "Relacion chofer,auto - error");

                }
            }
           
        }

        private void buscarChofer_Click(object sender, EventArgs e)
        {
            this.initializeChofer();
        }

        private void button2_Click(object sender, EventArgs e)
        {
            using (SqlConnection connection = new SqlConnection(conexion))
            {
                try
                {
                    using (SqlCommand command = new SqlCommand())
                    {
                        connection.Open();
                        command.Connection = connection;
                        command.CommandType = System.Data.CommandType.StoredProcedure;
                        command.CommandText = "TEQUILASO.sp_eliminarRelacionChoferAuto";
                        command.Parameters.AddWithValue("@IdAuto", Convert.ToInt32(dataGridView2.CurrentRow.Cells[0].Value.ToString()));
                        command.Parameters.AddWithValue("@IdChofer", Convert.ToInt32(dataGridView1.CurrentRow.Cells[0].Value.ToString()));
                        command.Parameters.AddWithValue("@Fecha", desdePicker.Value);
                        command.Parameters.AddWithValue("@DescripcionTurno", turnoComboBox.Text);
                        command.ExecuteReader();
                        connection.Close();

                        MessageBox.Show("Relacion eliminada con exito");

                        cargarRelacionChoferAuto();
                        dataGridChoferAuto.Refresh();


                    }
                }
                catch (SqlException ex)
                {
                    MessageBox.Show(ex.Message, "Relacion chofer,auto - error");

                }
            }
   
        }


    }
}
